
 -- **************************************************************************
--    Project Name:   服务质量-超标件网点汇总
--    Job Name:       jms_dm__dm_fwzl_over_nomarl_delivery_site_sum
--    Description :  运单获取的dws设备扫描的长、宽、高小于等于备案中的长、宽、高字段（长宽高取最大值进行比较），或者dws设备扫描的重量小于备案中的重量
--    Author :        jimmy 季修魁
--    date：          20223/05/22
-- **************************************************************************
--
--
-- **************************************************************************

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

insert overwrite table jms_dm.dm_fwzl_over_nomarl_delivery_site_sum partition (dt)
select to_date(t1.arrival_scan_time) as arrival_scan_time
       ,t1.pick_network_code
       ,t1.pick_network_name
       ,t1.pick_agent_code
       ,t1.pick_agent_name
       ,to_date(t1.delivery_time)           as delivery_time
       ,t1.over_nomarl_type
       ,t2.scan_center_arrival_cnt
       ,count(1)                   as  over_nomarl_cnt
       ,sum(case when t1.is_record='1' then 1 else 0 end ) as finish_record_cnt
       ,sum(case when t1.is_record='0' then 1 else 0 end ) as no_record_cnt
       ,t1.dt     as dt
  from jms_dm.dm_fwzl_over_nomarl_waybill_detail t1
  left join (select agent_code,sum(arrival_cnt) as scan_center_arrival_cnt,dt
               from jms_dm.dm_center_transfer_summary_dt
              where dt>= date_add('{{ execution_date | cst_ds }}',-2)
                and dt<= '{{ execution_date | cst_ds }}'
              group by agent_code,dt ) t2
    on t1.pick_agent_code=t2.agent_code
   and t1.dt= t2.dt
 where t1.dt >= date_add('{{ execution_date | cst_ds }}',-2)
   and t1.dt<='{{ execution_date | cst_ds }}'
  group by to_date(t1.arrival_scan_time)
       ,t1.pick_network_code
       ,t1.pick_network_name
       ,t1.pick_agent_code
       ,t1.pick_agent_name
       ,to_date(t1.delivery_time)
       ,t1.over_nomarl_type
       ,t2.scan_center_arrival_cnt
       ,t1.dt
  distribute by dt
  ;